use sqlx::{SqlitePool, sqlite::SqlitePoolOptions};
use anyhow::Result;
use std::path::PathBuf;
use tauri::AppHandle;

const DATABASE_URL: &str = "sqlite:multi_account_browser.db";

pub async fn init_db(app_handle: &AppHandle) -> Result<SqlitePool> {
    let app_dir = app_handle.path_resolver().app_data_dir()?;
    std::fs::create_dir_all(&app_dir)?;
    
    let db_path = app_dir.join("multi_account_browser.db");
    let database_url = format!("sqlite:{}", db_path.to_str().unwrap());
    
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect(&database_url)
        .await?;
    
    Ok(pool)
}

pub async fn create_tables(pool: &SqlitePool) -> Result<()> {
    sqlx::query(
        r#"
        CREATE TABLE IF NOT EXISTS accounts (
            id TEXT PRIMARY KEY,
            platform TEXT NOT NULL,
            username TEXT NOT NULL,
            password TEXT,
            status TEXT NOT NULL DEFAULT 'offline',
            last_active DATETIME,
            created_at DATETIME NOT NULL,
            cookies TEXT,
            notes TEXT
        )
        "#
    ).execute(pool).await?;

    sqlx::query(
        r#"
        CREATE TABLE IF NOT EXISTS platforms (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            logo TEXT,
            login_url TEXT NOT NULL,
            url_pattern TEXT,
            dom_selector TEXT,
            api_endpoint TEXT,
            is_enabled BOOLEAN NOT NULL DEFAULT true,
            created_at DATETIME NOT NULL
        )
        "#
    ).execute(pool).await?;

    sqlx::query(
        r#"
        CREATE TABLE IF NOT EXISTS tabs (
            id TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            url TEXT NOT NULL,
            account_id TEXT,
            is_active BOOLEAN NOT NULL DEFAULT false,
            favicon TEXT,
            created_at DATETIME NOT NULL,
            FOREIGN KEY (account_id) REFERENCES accounts (id)
        )
        "#
    ).execute(pool).await?;

    sqlx::query(
        r#"
        CREATE TABLE IF NOT EXISTS sessions (
            id TEXT PRIMARY KEY,
            account_id TEXT NOT NULL,
            cookies TEXT NOT NULL,
            user_agent TEXT,
            created_at DATETIME NOT NULL,
            expires_at DATETIME,
            FOREIGN KEY (account_id) REFERENCES accounts (id)
        )
        "#
    ).execute(pool).await?;

    Ok(())
}